﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Client.Logic.Upload;
using PPI.Entities;
using System.IO;
using System.Data;
using PPI.General;
using PPI.Mobile.Logic;
using VMind.Global;


namespace PPI.Client.Logic
{
    public class LVendor_Visit_XMissionStructures_Image
    {
        #region Query

        public static void AssignToAsset(EBaseAsset asset, List<EVendor_Visit_XMissionStructures_Image> imageList)
        {
            EVendor_Visit_XMissionStructures_Image _Image;
            foreach (EVendor_Visit_XMissionStructures_Image i in imageList)
            {
                if (i.Asset.Id == asset.Id)
                {
                    if(!asset.Images.Contains(i))
                    {
                        i.Asset = asset;
                        asset.Images.Add(i);
                    }
                    
                }
                    
            }
        }

        public static void AssignToPole(EBasePole pole, List<EVendor_Visit_XMissionStructures_Image> imageList)
        {
            foreach (EVendor_Visit_XMissionStructures_Image i in imageList)
            {
                if (i.Pole.Id != Int32.MinValue)
                {
                    if (pole.Id == i.Pole.Id)
                    {
                        if(!pole.Images.Contains(i))
                            pole.Images.Add(i);
                    }
                }
            }
        }

        public static DataTable GetDataTableByStructureID(int structureID)
        {
            
            return WebConsumer.WVendor_Visit_XMissionStructures_Image.GetByStructureID(structureID).Tables[0];
        }

        public static List<EVendor_Visit_XMissionStructures_Image> GetByStructure(int structureID)
        {
            DataTable _ImagesDatatable = GetDataTableByStructureID(structureID);
            return ParseDatatableImage(_ImagesDatatable);
        }

        public static List<EVendor_Visit_XMissionStructures_Image> GetServerImages()
        {
            DataTable _ImagesDatatable = WebConsumer.WVendor_Visit_XMissionStructures_Image.GetServer(0).Tables[0];
            return ParseDatatableImage(_ImagesDatatable);
        }

        public static List<EVendor_Visit_XMissionStructures_Image> ParseDatatableImage(DataTable dt)
        {
            EVendor_Visit_XMissionStructures_Image _Image;
            List<EVendor_Visit_XMissionStructures_Image> _Images = new List<EVendor_Visit_XMissionStructures_Image>();
            foreach (DataRow _ImageData in dt.Rows)
            {
                #region Images
                _Image = new EVendor_Visit_XMissionStructures_Image();
                _Image.Id = ConvertHelper.ToInt32(_ImageData["Vendor_Visit_XMissionStructures_ImageId"]);
                _Image.ServerID = ConvertHelper.ToInt32(_ImageData["Vendor_Visit_XMissionStructures_ImageId"]);
                _Image.Latitude = ConvertHelper.Todouble(_ImageData["Latitude"]);
                _Image.Longitude = ConvertHelper.Todouble(_ImageData["Longitude"]);
                _Image.Altitude = ConvertHelper.Todouble(_ImageData["Altitude"]);
                _Image.FileName = _ImageData["Filename"].ToString();
                _Image.SatelliteCount = ConvertHelper.Toint(_ImageData["SatelliteCount"]);
                _Image.SatelliteTime = ConvertHelper.ToDateTime(_ImageData["SatelliteTime"]);
                _Image.SolutionAvailable = ConvertHelper.Tobool(_ImageData["SolutionAvailable"]);
                _Image.Asset = new EVendor_Visit_XMissionStructure(ConvertHelper.ToInt32(_ImageData["Vendor_Visit_XMissionStructureFK"]));
                _Image.Pole = new EVendor_Visit_XMissionStructures_Pole(ConvertHelper.ToInt32(_ImageData["Vendor_Visit_XMissionStructures_PoleFK"]));
                #endregion Images
                if (!_Images.Contains(_Image))
                    _Images.Add(_Image);
            }
            return _Images;
        }

        #endregion

        #region Mobile

        #region Query

        public static Int32 CountMobileImages(string connectionString)
        {
            StringBuilder _QueryMobile = new StringBuilder();
            _QueryMobile.AppendLine("select Count(*) from  tb_Vendor_Visit_Images");
            return (Int32)LMobileDatabase.ExecuteScalar(_QueryMobile.ToString(), connectionString);


        }

        public static List<EVendor_Visit_XMissionStructures_Image> GetByVisit(EVendor_Visit_XMissionStructure visit, string connectionString)
        {
            StringBuilder _query = new StringBuilder();
            #region Query

            _query.AppendLine(" SELECT ");
            _query.AppendLine("    IM.ImageId,");
            _query.AppendLine("    IM.VisitFk,");
            _query.AppendLine("    IM.PoleFk,");
            _query.AppendLine("    IM.Latitude,");
            _query.AppendLine("    IM.Longitude,");
            _query.AppendLine("    IM.Altitude,");
            _query.AppendLine("    IM.SatelliteCount,");
            _query.AppendLine("    IM.SatelliteTime,");
            _query.AppendLine("    IM.SolutionAvailable,");
            _query.AppendLine("    IM.Filename,");
            _query.AppendLine("    IM.ImageServerID,");
            _query.AppendLine("    IM.PoleServerID as PoleServerID,");
            _query.AppendLine("    IM.StructureServerID,");
            _query.AppendLine("    IM.UploadedDate ");
            _query.AppendLine(" FROM");
            _query.AppendLine("    tb_Vendor_Visit_Images AS IM");
            
            _query.AppendLine("WHERE");
            _query.AppendLine(" IM.VisitFk = " + visit.Id + " ");


            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_query.ToString(), connectionString).Tables[0];

            return ParsetMobileImages(dt,connectionString);

        }

        public static List<EVendor_Visit_XMissionStructures_Image> GetAll(string connectionString)
        {
            StringBuilder _query = new StringBuilder();

            #region Query

            _query.AppendLine(" SELECT ");
            _query.AppendLine("    IM.ImageId,");
            _query.AppendLine("    IM.VisitFk,");
            _query.AppendLine("    IM.PoleFk,");
            _query.AppendLine("    IM.Latitude,");
            _query.AppendLine("    IM.Longitude,");
            _query.AppendLine("    IM.Altitude,");
            _query.AppendLine("    IM.SatelliteCount,");
            _query.AppendLine("    IM.SatelliteTime,");
            _query.AppendLine("    IM.SolutionAvailable,");
            _query.AppendLine("    IM.Filename,");
            _query.AppendLine("    IM.ImageServerID,");
            _query.AppendLine("    IM.PoleServerID as PoleServerID,");
            _query.AppendLine("    IM.StructureServerID,");
            _query.AppendLine("    IM.UploadedDate ");
            _query.AppendLine(" FROM");
            _query.AppendLine("    tb_Vendor_Visit_Images AS IM");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_query.ToString(), connectionString).Tables[0];

            return ParsetMobileImages(dt,connectionString);

        }

        private static List<EVendor_Visit_XMissionStructures_Image> ParsetMobileImages(DataTable dt, string connectionString)
        {
            List<EVendor_Visit_XMissionStructures_Image> _imagesList = new List<EVendor_Visit_XMissionStructures_Image>();

            foreach (DataRow dr in dt.Rows)
            {
                EVendor_Visit_XMissionStructures_Image _tmpImage = new EVendor_Visit_XMissionStructures_Image();
                _tmpImage.SolutionAvailable = ConvertHelper.Tobool(dr["SolutionAvailable"]);
                _tmpImage.SatelliteTime = ConvertHelper.ToDateTime(dr["SatelliteTime"]);
                _tmpImage.ServerID = ConvertHelper.ToInt32(dr["ImageServerID"]);
                _tmpImage.Pole = new EVendor_Visit_XMissionStructures_Pole();
                
                
                _tmpImage.Pole.Id = ConvertHelper.ToInt32(dr["PoleFk"]);
                _tmpImage.Pole.ServerID = ConvertHelper.ToInt32(dr["PoleServerID"]);
                _tmpImage.Asset = new EVendor_Visit_XMissionStructure();

                _tmpImage.Asset.Id = ConvertHelper.ToInt32(dr["VisitFk"]);
                _tmpImage.Asset.ServerID = ConvertHelper.ToInt32(dr["StructureServerID"]);
                _tmpImage.ServerID = ConvertHelper.ToInt32(dr["ImageServerID"]);
                _tmpImage.Id = ConvertHelper.ToInt32(dr["ImageId"]);
                _tmpImage.ImageFile = new FileInfo(DirHelper.GetImagesDirectory(connectionString) + Path.DirectorySeparatorChar + dr["Filename"].ToString());
                _imagesList.Add(_tmpImage);
            }
            return _imagesList;


        }

        #endregion

        #region CRUD
        
        public static void UpdateUploadedData(EVendor_Visit_XMissionStructures_Image image, string connectionString)
        {
            StringBuilder _query = new StringBuilder();

            _query.AppendLine("update tb_Vendor_Visit_Images set ImageServerID =" + image.ServerID + ",UploadedDate = " +
                              DateTime.Now.ToString("yyyy-MM-dd") + " where ImageID =" + image.Id + " ");

            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
        }

        public static void DeleteMobile(EVendor_Visit_XMissionStructures_Image image, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();

            #region Query
            _Query.AppendLine("Delete tb_Vendor_Visit_Images where ImageId = @ImageId");
            #endregion


            List<SqlParameter> listParameter = new List<SqlParameter>();

            #region Parameters

            listParameter.Add(DBHelper.CreateIntParameter("@ImageId",ParameterDirection.Input,false,image.Id));

            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());



        }

        public static Int32 SaveMobile(EVendor_Visit_XMissionStructures_Image image, string connectionString)
        {
            Int32 returnId = image.Id;
            if(image.Id != Int32.MinValue)
            {
                UpdateMobile(image, connectionString);
            }else
            {
                image.Id = InsertMobile(image, connectionString);
                returnId = image.Id;
                string newImageName = image.Id + ".jpg";
                string newImagePath = DirHelper.CopyImageToOutbox(connectionString, image.ImageFile.FullName, newImageName);
                image.ImageFile = new FileInfo(newImagePath); 
                UpdateFileName(newImageName, image.Id, connectionString);
            }
            return returnId;
        }

        private static int InsertMobile(EVendor_Visit_XMissionStructures_Image image, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("Insert into tb_Vendor_Visit_Images ( ");
            _Query.AppendLine("     visitFk,SolutionAvailable,FileName,PoleFk ");
            _Query.AppendLine("     )Values(@visitFk,@SolutionAvailable,@FileName,@PoleFk)");
            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();

            #region Parameters
           
            listParameters.Add(DBHelper.CreateIntParameter("@visitFk", ParameterDirection.Input, false, image.Asset.Id));
            listParameters.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, image.SolutionAvailable));
            listParameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 255, ParameterDirection.Input, false, image.ImageFile.Name));

            if (image.Pole.Id != Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, image.Pole.Id));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, true, DBNull.Value));


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString,listParameters.ToArray());

            _Query = new StringBuilder(" select max(ImageId) from tb_Vendor_Visit_Images");
            return (Int32)LMobileDatabase.ExecuteScalar(_Query.ToString(),connectionString);

        }

        public static void InsertMobileProject(EVendor_Visit_XMissionStructures_Image image, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("Insert into tb_Vendor_Visit_Images ( ");
            _Query.AppendLine("     ImageId,visitFk,SolutionAvailable,FileName,PoleFk ");
            _Query.AppendLine("     )Values(@ImageId,@visitFk,@SolutionAvailable,@FileName,@PoleFk)");
            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();

            #region Parameters

            listParameters.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, image.Id));
            listParameters.Add(DBHelper.CreateIntParameter("@visitFk", ParameterDirection.Input, false, image.Asset.Id));
            listParameters.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, image.SolutionAvailable));
            listParameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 255, ParameterDirection.Input, false, image.ImageFile.Name));

            if (image.Pole.Id != Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, image.Pole.Id));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, true, DBNull.Value));


            #endregion

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameters.ToArray());
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        public static int GetNextImageId(string connectionString)
        {
            StringBuilder _Query;
            _Query = new StringBuilder(" select max(ImageId) from tb_Vendor_Visit_Images");
            return (Int32)LMobileDatabase.ExecuteScalar(_Query.ToString(), connectionString);
        }

        private static void UpdateMobile(EVendor_Visit_XMissionStructures_Image image, string connectionString)
        {

            StringBuilder _Query = new StringBuilder();

            #region Query

            _Query.AppendLine("update tb_Vendor_Visit_Images SET");
            _Query.AppendLine("     VisitFk = @VisitFk,");
            _Query.AppendLine("     PoleFk = @PoleFk");
            _Query.AppendLine("where ImageId = @ImageId");


            #endregion

            #region Parameters
            List<SqlParameter> listParameters = new List<SqlParameter>();
            listParameters.Add(DBHelper.CreateIntParameter("@VisitFk", ParameterDirection.Input, false, image.Asset.Id));
            listParameters.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, image.Id));

            if(image.Pole.Id != Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, image.Pole.Id));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, true, DBNull.Value));


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString,listParameters.ToArray());
        }

        private static void UpdateFileName(string fileName,Int32 idImage,string connectionString)
        {
            StringBuilder _Query = new StringBuilder("Update tb_Vendor_Visit_Images SET Filename = @FileName where ImageId = @ImageId  ");

            List<SqlParameter> listParameter = new List<SqlParameter>();
            listParameter.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, idImage));
            listParameter.Add(DBHelper.CreateNVarCharParameter("@FileName", 255, ParameterDirection.Input, false, fileName));

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());
             


        }

        #endregion

        #endregion

        #region CRUD

        public static Int32 Save(EVendor_Visit_XMissionStructures_Image image)
        {
            string _Filename = image.Asset.Id + "_" + image.Id + ".jpg";
            image.Id = WebConsumer.WVendor_Visit_XMissionStructures_Image.Save(image.Id, image.Asset.ServerID,
                                                                        image.SolutionAvailable, _Filename,
                                                                        image.Latitude, image.Altitude, image.Longitude,
                                                                        image.SatelliteTime, image.SatelliteCount,
                                                                        double.MinValue, image.Pole.ServerID);
            return image.Id;
                

        }

        public static void Delete(EVendor_Visit_XMissionStructures_Image image)
        {
            WebConsumer.WVendor_Visit_XMissionStructures_Image.Delete(image.Id,image.FileName);
        }

        #endregion
    }
}
